package tableprocessor;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import org.hibernate.FlushMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 *
 * @author Gleb
 */
public class DBProduceDAO
{
    private final static Logger logger = LoggerFactory.getLogger(DBProduceDAO.class);
    public static Iterator<DBProduce> getProduceByCathegory(int cid)
    {
        Transaction tx = null;
        List <DBProduce> rlist = null;
        Session session = SessionFactoryUtil.getInstance().getCurrentSession();
        try
        {
            session.setFlushMode(FlushMode.NEVER);
            tx = session.beginTransaction();
            Query q = session.createQuery("SELECT rlist FROM DBProduce AS rlist WHERE cathegory_id =:cid");//"SELECT rlist FROM DBProduce AS rlist ");
            q.setParameter("cid", cid);
            rlist = q.list();
            tx.commit();
            return rlist.iterator();
        }
        catch (RuntimeException e)
        {
            e.printStackTrace();
            if (tx != null && tx.isActive())
            {
                try {tx.rollback();}
                catch (HibernateException e1){e1.printStackTrace();}
            }
        }
        return null;
    }
    public static void clear()
    {
        Session session = SessionFactoryUtil.getInstance().getCurrentSession();
        Transaction tx = null;
        try
        {
            session.setFlushMode(FlushMode.NEVER);
            tx = session.beginTransaction();
            Query query = session.createQuery("DELETE FROM DBProduce");
            query.executeUpdate();
            tx.commit();

        }
        catch (RuntimeException e)
        {
            e.printStackTrace();
        }
    }
    public static void clearByCathegoryId(int cid)
    {
                Session session = SessionFactoryUtil.getInstance().getCurrentSession();
        Transaction tx = null;
        try
        {
            session.setFlushMode(FlushMode.NEVER);
            tx = session.beginTransaction();
            Query query = session.createQuery("DELETE FROM DBProduce WHERE cathegory_id =:cid");
            query.setParameter("cid", cid);
            query.executeUpdate();
            tx.commit();

        }
        catch (RuntimeException e)
        {
            e.printStackTrace();
        }
    }
    public static DBProduce getProduceByCathegoryColumnRow(int catid, int colid, int row)
    {
        DBProduce produce = null;
        Transaction tx = null;
        List<DBProduce> list = new ArrayList<DBProduce>();
        Session session = SessionFactoryUtil.getInstance().getCurrentSession();
        try
        {
            session.setFlushMode(FlushMode.NEVER);
            tx = session.beginTransaction();
            Query q = session.createQuery("SELECT list FROM DBProduce AS list WHERE cathegory_id =:catid AND column_id=:colid AND row=:row");
            q.setParameter("catid",catid);
            q.setParameter("colid",colid);
            q.setParameter("row",row);
            list = q.list();
            tx.commit();
            if (!list.isEmpty())
            return list.get(0);
            return null;
        }
        catch (RuntimeException e)
        {
            e.printStackTrace();
            if (tx != null && tx.isActive())
            {
                try {tx.rollback();}
                catch (HibernateException e1){e1.printStackTrace();}
            }
        }
        return produce;
    }
    public static int getRowCountForCathegory(int cid)
    {
        int rez = 0;
        Transaction tx = null;
        List  rlist;
        Session session = SessionFactoryUtil.getInstance().getCurrentSession();
        try
        {
            session.setFlushMode(FlushMode.NEVER);
            tx = session.beginTransaction();
            Query q = session.createQuery("SELECT MAX(row) FROM DBProduce produce WHERE cathegory_id =:cid");//"SELECT rlist FROM DBProduce AS rlist ");
            q.setParameter("cid", cid);
            rlist = q.list();
            tx.commit();
            if (rlist!= null && !rlist.isEmpty()&& rlist.get(0)!=null)
            {
               return (Integer)rlist.get(0);
            }
            return rez;
        }
        catch (RuntimeException e)
        {
            e.printStackTrace();
            if (tx != null && tx.isActive())
            {
                try {tx.rollback();}
                catch (HibernateException e1){/*e1.printStackTrace();*/}
            }
        }
        return rez;
    }
    public static Iterator<Integer> getProduceList()
    {
        Transaction tx = null;
        List <DBProduce> rlist = null;
        Session session = SessionFactoryUtil.getInstance().getCurrentSession();
        try
        {
            session.setFlushMode(FlushMode.NEVER);
            tx = session.beginTransaction();
            rlist = (List<DBProduce>)session.createQuery("FROM DBProduce").list();//"SELECT rlist FROM DBProduce AS rlist ");
            //rlist = q.list();
            tx.commit();
            Set<Integer> set = new LinkedHashSet<Integer>();
            for (int i = 0; i < rlist.size(); i++)
            {
                set.add(rlist.get(i).getCathegory_id());
            }
            return set.iterator();
        }
        catch (RuntimeException e)
        {
            e.printStackTrace();
            if (tx != null && tx.isActive())
            {
                try {tx.rollback();}
                catch (HibernateException e1){e1.printStackTrace();}
            }
        }
        return null;
    }
    public static void addProduce(String _value, int _cathegory_id,int _column_id, int _row)
    {
            Transaction tx = null;
            Session session = SessionFactoryUtil.getInstance().getCurrentSession();
            try
            {
               // session.setFlushMode(FlushMode.NEVER);
                tx = session.beginTransaction();
                session.save(new DBProduce(_value,_cathegory_id ,_column_id, _row));
                tx.commit();
            }
            catch (RuntimeException e)
            {
                if (tx != null && tx.isActive())
                {
                    try {tx.rollback();}
                    catch (HibernateException e1){e1.printStackTrace();}
                }
            }
    }
}
